#!/usr/bin/env python
#-*- coding:utf-8 -*-


'''
MySQL数据获取模型
    author : cold night
    email  : wh_linux@126.com
'''


from model.get_db import get_db
from model.common import not_empty
from common import dict_to_sql_select
from setting import page_size


__all__=['get_articles','get_categories', 'get_category', 'get_comments', 'get_artdate']

conn = get_db()
cursor = conn.cursor()
POSTS='posts'
POSTSMETA = 'postmeta'
TAGS = 'tags'
COMM = 'comments'
CATE = 'categories'


def _format_result(result, keys, **kargs):
    r = {}
    if len(result) == len(keys):
        i=0
        for key in keys:
            r[key] = result[i]
            i+=1
    else:
        raise ValueError("key's length not equal result's length")
    r.update(kargs)
    return r


def _get_tags(artid):
    sql = 'select `name` from tags WHERE `artid`=%s'
    param = (artid,)
    cursor.execute(sql, param)
    tags = map(lambda x:x[0], cursor.fetchall())
    return tags


def _get_categories(artid):
    sql = 'select `value` from postsmeta WHERE `artid`=%s and `key`="category"'
    param = (artid,)
    cursor.execute(sql, param)
    r = cursor.fetchall()
    _ids = map(lambda x:x[0], r)
    result = []

    for _id in _ids:
        sql = 'select `_id`, `name` from categories where `_id`=%s'
        param = (_id,)
        cursor.execute(sql, param)
        _r = cursor.fetchone()
        result.append(dict(_id=_r[0], name=_r[1]))

    return result


def get_articles(artid = None, index=1, size = page_size, addviews = False, **kargs):
    if artid:
        kargs.update(dict(_id=artid))
        
    sql = dict_to_sql_select(POSTS, kargs) + 'order by `_id` desc limit %s,%s'
    param = ((index-1) * size, size)
    cursor.execute(sql, param)
    keys = ['_id', 'author', 'title', 
            'content', 'views', 'date', 'status','lastdate']
    result = cursor.fetchall()

    if artid:
        r = result[0]
        _r = _format_result(r, keys)
        _r['tags'] = _get_tags(r[0])
        _r['categories'] = _get_categories(r[0])
        _r['comments'] = _get_comments_num(r[0])
        return _r
    else:
        sql = 'select count(*) from posts'
        param = (POSTS,)
        cursor.execute(sql)
        count = cursor.fetchone()[0]
        articles = []
        for r in result:
            _r = _format_result(r, keys)             
            _r['tags'] = _get_tags(r[0])
            _r['categories'] = _get_categories(r[0])
            _r['comments'] = _get_comments_num(r[0])
            articles.append(_r)
            
        return articles, count



def _get_artnum(cateid):
    sql = 'select count(*) from postsmeta where `key`="category" and `value`=%s'
    param = (cateid,)
    cursor.execute(sql, param)
    count = cursor.fetchone()[0]
    return count



def get_categories(index = 1, size = page_size):
    keys = ['_id', 'name', 'date']
    sql = 'select `_id`, `name`, `date` FROM categories order by `_id` desc limit %s,%s '
    param = ((index-1)*size, size)
    cursor.execute(sql, param)
    result = cursor.fetchall()
    

    cate = []
    for r in result:
        resu = _format_result(r, keys)
        count = _get_artnum(r[0])
        resu['artnum'] = count
        cate.append(resu)

    return cate



def get_category(_id):
    not_empty(_id)
    keys = ['_id', 'name']
    sql = 'select `_id`, `name` from categories WHERE `_id`=%s'
    param = (_id)
    cursor.execute(sql, param)
    result = _format_result(cursor.fetchone(), keys)
    count = _get_artnum(result.get('_id'))
    result.update(dict(artnum = count))
    return result



def _get_comments_num(artid = None):
    if artid:
        csql = 'select count(*) from comments WHERE `artid`=%s'
        cparam = (artid,)
    else:
        csql = 'select count(*) from comments'
        cparam = ()

    cursor.execute(csql, cparam)
    count = cursor.fetchone()[0]
    return count

        

def get_comments(artid = None, index = 1, size = page_size):
    keys = ('_id', 'name', 'email', 'website', 'content',
            'artid', 'date', 'parent_id', 'status')
    if artid:
        sql = 'select * from comments WHERE `artid`=%s order by `_id` desc limit %s,%s'
        param = (artid, (index-1)*size, size)
    else:
        sql = 'select * from comments order by `_id` desc limit %s, %s'
        param = ((index-1)*size, size)

        
    cursor.execute(sql, param)
    result = cursor.fetchall()
    comments = [_format_result(r, keys) for r in result]
    
    for comm in comments:
        article = get_articles(comm.get('artid'))
        comm['title'] = article.get('title')
    count = _get_comments_num(artid)
    return comments, count


def get_artdate():
    sql = 'select `date` from posts'
    cursor.execute(sql)
    result = [(str(r[0].year)+'-'+str(r[0].month)) for r in cursor.fetchall()]

    dates = list(set(result))
    return dates
        
